package com.sj.purchase.modules.cost.repository.order;

import com.sj.purchase.modules.cost.domain.WarehouseEnum;
import com.sj.purchase.modules.cost.domain.order.InOrderItem;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
 * Created by yangrd on 2017/7/19.
 */
public interface InOrderItemRepository extends JpaRepository<InOrderItem, String> {

    Page<InOrderItem> findByOrderCreateDateBetweenAndSkuNoContaining(Date start, Date end, String skuNo, Pageable pageable);

    /**
     * 获取某天每一个仓库sku的库存
     */
    @Query("select item.skuNo, item.order.warehouseEnum,  sum(item.num) from InOrderItem item where item.skuNo in ?2 and item.order.saveDateDay = ?1 group by item.skuNo ,item.order.warehouseEnum")
    List<Object[]> listSkuNumGroupByWarehouse(Date day, Collection<String> skuNos);

    @Query("select item.skuNo, sum(item.num) from InOrderItem item where item.skuNo in ?2 and item.order.saveDateDay = ?1 group by item.skuNo")
    List<Object[]> listSkuNumGroupBySkuNo(Date day, Collection<String> skuNos);


    /**
     * 查询入库单订单sku唯一码成本价
     *
     * @param day
     * @param orderNo
     * @param sku
     * @return
     */
    @Query("select AVG(t.price) from InOrderItem t  where t.order.orderNo=?2 and t.order.saveDateDay=?1 and t.skuNo=?3")
    Double findCost(Date day, String orderNo, String sku);

    @Query("select t.price from InOrderItem t  where t.order.orderNo=?1  and t.skuNo=?2")
    Double findCost(String orderNo, String sku);

    @Query("select a.skuNo,a.order.type,sum(a.num) from InOrderItem a where a.order.warehouseEnum = ?1 and a.order.saveDateDay between ?2 and ?3 and a.skuNo in ?4 group by a.skuNo,a.order.type")
    List<Object[]> findSkuAndOrderTypeAndNum(WarehouseEnum warehouseEnum, Date start, Date end, List<String> skuNos);
}
